* BEA_proc.do
* 2014.10.22
* Last update 2014.10.22: script created
* Builds county and state time series from BEA data

capture log close
set more off
timer clear 1
timer on 1
clear
set matsize 10000

local work "/DIRECTORY"

log using "`work'/logs/BEA_proc.log", replace


******************************
* Counties
******************************
* Income-population files
foreach x in "2003_2007" "2008_2012" {
	insheet using "`work'/data/bea reis/County_income_pop_`x'.csv", comma names clear
	if "`x'"=="2003_2007" {
		forval i = 5/9 {
			local j = `i' + 1998
			rename v`i' v`j'
		}
	}
	else {
		forval i = 5/9 {
			local j = `i' + 2003
			rename v`i' v`j'
		}
	}
	gen var = ""
	replace var = "income" if linecode==1
	replace var = "pop" if linecode==2
	replace var = "incpercapita" if linecode==3
	drop if missing(var) /* drops footnotes */
	reshape long v, i(geofips geoname linecode) j(year)
	drop linecode description
	reshape wide v, i(geofips geoname year) j(var) string
	rename vincome income 
	rename vincpercapita  inc_per_capita
	rename vpop population
	tempfile pt`x'
	save "`pt`x''", replace
}
use "`pt2003_2007'", clear
append using "`pt2008_2012'"
encode geofips, generate(FIPS)
xtset FIPS year, yearly
save "`work'/data/bea reis/County_inc_pop.dta", replace

* Employment files
foreach x in "2003_2007" "2008_2012" {
	insheet using "`work'/data/bea reis/County_employment_`x'.csv", comma names clear
	if "`x'"=="2003_2007" {
		forval i = 3/7 {
			local j = `i' + 2000
			rename v`i' v`j'
		}
	}
	else {
		forval i = 3/7 {
			local j = `i' + 2005
			rename v`i' v`j'
		}
	}
	drop if missing(geoname) /* drops footnotes */
	reshape long v, i(geofips geoname) j(year)
	rename v employment
	tempfile pt`x'
	save "`pt`x''", replace
}
use "`pt2003_2007'", clear
append using "`pt2008_2012'"
encode geofips, generate(FIPS)
xtset FIPS year, yearly
save "`work'/data/bea reis/County_employment.dta", replace

* Merging
merge 1:1 FIPS year using "`work'/data/bea reis/County_inc_pop.dta", nogenerate
rename geofips fips
drop geoname
foreach x in employment income inc_per_capita population {
	destring `x', replace force /* need force because missing data are denoted with "(NA)" */
}
compress
save "`work'/data/bea reis/County_BEA.dta", replace


******************************
* States
******************************
* Income-population files
insheet using "`work'/data/bea reis/State_income_pop_2003_2013.csv", comma names clear
forval i = 5/15 {
	local j = `i' + 1998
	rename v`i' v`j'
}
gen var = ""
replace var = "income" if linecode==1
replace var = "pop" if linecode==2
replace var = "incpercapita" if linecode==3
drop if missing(var) /* drops footnotes */
reshape long v, i(geofips geoname linecode) j(year)
drop linecode description
reshape wide v, i(geofips geoname year) j(var) string
rename vincome income 
rename vincpercapita  inc_per_capita
rename vpop population
encode geofips, generate(FIPS)
tempfile stateinc
save "`stateinc'", replace

* Employment files
insheet using "`work'/data/bea reis/State_employment_2003_2013.csv", comma names clear
forval i = 3/13 {
	local j = `i' + 2000
	rename v`i' v`j'
}
drop if missing(geoname) /* drops footnotes */
reshape long v, i(geofips geoname) j(year)
rename v employment
encode geofips, generate(FIPS)

* Merging
merge 1:1 FIPS year using "`stateinc'", nogenerate
rename geofips fips
gen gestfips = substr(fips, 1, 2)
drop geoname
xtset FIPS year
compress
save "`work'/data/bea reis/State_BEA.dta", replace





timer off 1
timer list 1
capture log close



